CREATE PROCEDURE [NEOE].[UP_FASHION_FI_ADOCU_ASSET_I] ( @CD_COMPANY NVARCHAR(7) = '1000' , @DOCU_TYPE CHAR(4) = 'BOS1' , @SettleList SettleComplete READONLY , @RESULT INT OUT ) AS /* FOR TEST DECLARE @CD_COMPANY NVARCHAR(7) DECLARE @Docu_Type CHAR(4) DECLARE @SettleList SettleComplete SET @CD_Company = '1000' SET @Docu_Type = 'BOS2'n insert into @SettleList VALUES('1898700338','2019-05-31',-454700.0000,6230.0000,62295.0000,386175.0000) */ /* 기존 5자리 계정코드에서 8자리 코드로 변경 2019 06 12 30310=>21102010 : 미지급금(거래처) 50100=>51102010 : 상품매출 30500=>21604010 : 부가세예수금 51100=>51107010 : 수수료매출 51102010<=>21604010 */ /* 담당자 사번 변경 자금예정일자 추가 */ DECLARE @CD_PC NVARCHAR(7) DECLARE @CD_WDEPT NVARCHAR(12) DECLARE @ID_WRITE NVARCHAR(20) DECLARE @CD_CC VARCHAR(6) --추가2019 07 17 DECLARE @UCD_MNG1 VARCHAR(3) --추가2019 07 17 DECLARE @ROW_ID_PREFIX CHAR(2) DECLARE @NM_ITEM_DESC NVARCHAR(20) DECLARE @DT_END VARCHAR(8) --추가 2020 02 20 SET @CD_PC = '1000' --SET @CD_WDEPT = '1102' --SET @ID_WRITE = '2017050102' SET @CD_WDEPT = '15000' --변경 2019 07 17 SET @ID_WRITE = '2019071012'--변경 2019 07 17 2020 02 20 변경 SET @CD_CC = '500001'--추가2019 07 17 SET @UCD_MNG1 = '890'--추가2019 07 17 SET @DT_END = CONVERT(CHAR(6), GETDATE(), 112) + '15'--추가 2020 02 20 당월 15일로 세팅 BEGIN IF ISNULL(@DOCU_TYPE, 'BOS1') = 'BOS1' BEGIN SET @ROW_ID_PREFIX = 'IM' SET @NM_ITEM_DESC = '월 상품판매수수료' END ELSE BEGIN SET @ROW_ID_PREFIX = 'ID' SET @NM_ITEM_DESC = '월 배송비' END BEGIN TRY IF @@TRANCOUNT > 0 BEGIN SET @RESULT = @@TRANCOUNT END INSERT INTO NEOE.FI_ADOCU (ROW_ID, ROW_NO, NO_TAX, CD_PC, CD_WDEPT , NO_DOCU, NO_DOLINE, CD_COMPANY, ID_WRITE, CD_DOCU , DT_ACCT, ST_DOCU, TP_DRCR, CD_ACCT, AMT , CD_PARTNER, NM_PARTNER, TP_JOB, CLS_JOB, ADS_HD , NM_CEO, DT_START,DT_END, AM_TAXSTD, AM_ADDTAX, TP_TAX , NO_COMPANY, NM_NOTE, CD_BIZAREA, CD_DEPT, CD_CC, NO_DEPOSIT--추가2019 07 17 , CD_BANK,UCD_MNG1, CD_MNG, TP_DOCU, NO_ACCT, TP_GUBUN--추가2019 07 17 , NM_ITEM1, NM_SIZE1, AM_SUPPLY1, AM_TAX1, NM_PUMM , CD_MNGD1, CD_MNGD2, CD_MNGD3, CD_MNGD4, CD_MNGD5 , CD_MNGD6, CD_MNGD7, CD_MNGD8, NM_MNGD1, NM_MNGD2 , NM_MNGD3, NM_MNGD4, NM_MNGD5, NM_MNGD6, NM_MNGD7 , NM_MNGD8 ) SELECT @ROW_ID_PREFIX + S.SettleDate + REPLICATE('0', 5 - LEN(ROW_NO)) + CAST(ROW_NO AS VARCHAR(100)) AS ROW_ID , ROW_NUMBER() OVER(PARTITION BY S.BizNumber, S.SettleDate ORDER BY S.BizNumber, S.CD_ACCT) AS ROW_NO , CASE WHEN S.CD_ACCT = '21604010'/*51102010*/ THEN 'T' + S.SettleDate + REPLICATE('0', 5 - LEN(ROW_NO)) + CAST(ROW_NO AS VARCHAR(10)) ELSE '*' END AS NO_TAX , @CD_PC AS CD_PC , @CD_WDEPT AS CD_WDEPT , @ROW_ID_PREFIX + S.SettleDate + REPLICATE('0', 5 - LEN(ROW_NO)) + CAST(ROW_NO AS VARCHAR(100)) AS NO_DOCU , ROW_NUMBER() OVER(PARTITION BY S.BizNumber, S.SettleDate ORDER BY S.BizNumber, S.CD_ACCT) AS NO_DOLINE , @CD_PC AS CD_COMPANY , @ID_WRITE AS ID_WRITE , '11' AS CD_DOCU , S.SettleDate AS DT_ACCT , '1' AS ST_DOCU , AC.TP_DRCR , S.CD_ACCT , ROUND(AMT, 0) AS AMT , P.CD_PARTNER , P.LN_PARTNER AS NM_PARTNER , CONVERT(NVARCHAR(40), P.TP_JOB) AS TP_JOB , CONVERT(NVARCHAR(40), P.CLS_JOB) AS CLS_JOB , P.DC_ADS1_H AS ADS_HD , P.NM_CEO , CASE WHEN S.CD_ACCT = '21604010' THEN S.SettleDate ELSE '' END AS DT_START ,@DT_END AS DT_END--추가 2020 02 20 , CASE WHEN S.CD_ACCT = '21604010' THEN C.SupAmount ELSE 0 END AS AM_SUPPLY1 , CASE WHEN S.CD_ACCT = '21604010' THEN S.AMT ELSE 0 END AS AM_ADDTAX , CASE WHEN S.CD_ACCT = '21604010' THEN '11' ELSE '' END AS TP_TAX , CASE WHEN S.CD_ACCT = '21604010' THEN P.NO_COMPANY ELSE '' END AS NO_COMPANY , CASE WHEN @Docu_Type = 'BOS1' THEN '[패션]'+CAST(MONTH(SettleDate) AS VARCHAR)+ '월 상품판매대금정산//'+ P.LN_PARTNER WHEN @Docu_Type = 'BOS2' THEN '[패션]'+CAST(MONTH(SettleDate) AS VARCHAR)+ '월 배송비정산//'+ P.LN_PARTNER END AS NM_NOTE --적요 수정 이선미 , @CD_PC AS CD_BIZAREA , @CD_WDEPT AS CD_DEPT , @CD_CC AS CD_CC --추가2019 07 09 --, CASE WHEN S.CD_ACCT = '30500' THEN P.NO_DEPOSIT ELSE '' END AS NO_DEPOSIT , '' AS NO_DEPOSIT --, CASE WHEN S.CD_ACCT = '30500' THEN P.CD_BANK ELSE '' END AS CD_BANK , '' AS CD_BANK , @UCD_MNG1 AS UCD_MNG1 --추가2019 07 09 , CASE WHEN S.CD_ACCT = '21604010' THEN 'T' + S.SettleDate + REPLICATE('0', 5 - LEN(ROW_NO)) + CAST(ROW_NO AS VARCHAR(10)) ELSE '' END AS CD_MNG , 'N' AS TP_DOCU , 0 AS NO_ACCT , '3' AS TP_GUBUN , RIGHT(LEFT(S.SettleDate,6), 2) + @NM_ITEM_DESC AS NM_ITEM1 , '' AS NM_SIZE1 , CASE WHEN S.CD_ACCT = '21604010' THEN C.SupAmount ELSE 0 END AS AM_SUPPLY1 , CASE WHEN S.CD_ACCT = '21604010' THEN S.AMT ELSE 0 END AS AM_TAX1 , CASE WHEN @Docu_Type = 'BOS1' THEN '[패션]'+CAST(MONTH(SettleDate) AS VARCHAR)+ '월 상품판매대금정산//'+ P.LN_PARTNER WHEN @Docu_Type = 'BOS2' THEN '[패션]'+CAST(MONTH(SettleDate) AS VARCHAR)+ '월 배송비정산//'+ P.LN_PARTNER END AS NM_PUMM --적요 수정 , CASE WHEN AC.ST_MNG1 = 'C' THEN CASE WHEN AC.CD_MNG1 = 'A02' THEN '6000' ELSE '' END ELSE '' END AS CD_MNGD1 , CASE WHEN AC.ST_MNG2 = 'C' THEN CASE WHEN AC.CD_MNG2 = 'A02' THEN '6000' ELSE '' END ELSE '' END AS CD_MNGD2 , CASE WHEN AC.ST_MNG3 = 'C' THEN CASE WHEN AC.CD_MNG3 = 'A02' THEN '6000' ELSE '' END ELSE '' END AS CD_MNGD3 , CASE WHEN AC.ST_MNG4 = 'C' THEN CASE WHEN AC.CD_MNG4 = 'A02' THEN '6000' ELSE '' END ELSE '' END AS CD_MNGD4 , CASE WHEN AC.ST_MNG5 = 'C' THEN CASE WHEN AC.CD_MNG5 = 'A02' THEN '6000' ELSE '' END ELSE '' END AS CD_MNGD5 , CASE WHEN AC.ST_MNG6 = 'C' THEN CASE WHEN AC.CD_MNG6 = 'A02' THEN '6000' ELSE '' END ELSE '' END AS CD_MNGD6 , CASE WHEN AC.ST_MNG7 = 'C' THEN CASE WHEN AC.CD_MNG7 = 'A02' THEN '6000' ELSE '' END ELSE '' END AS CD_MNGD7 , CASE WHEN AC.ST_MNG8 = 'C' THEN CASE WHEN AC.CD_MNG8 = 'A02' THEN '6000' ELSE '' END ELSE '' END AS CD_MNGD8 , CASE WHEN AC.ST_MNG1 = 'C' THEN CASE WHEN AC.CD_MNG1 = 'A02' THEN '패션사업본부' ELSE '' END ELSE '' END AS NM_MNGD1 , CASE WHEN AC.ST_MNG2 = 'C' THEN CASE WHEN AC.CD_MNG2 = 'A02' THEN '패션사업본부' ELSE '' END ELSE '' END AS NM_MNGD2 , CASE WHEN AC.ST_MNG3 = 'C' THEN CASE WHEN AC.CD_MNG3 = 'A02' THEN '패션사업본부' ELSE '' END ELSE '' END AS NM_MNGD3 , CASE WHEN AC.ST_MNG4 = 'C' THEN CASE WHEN AC.CD_MNG4 = 'A02' THEN '패션사업본부' ELSE '' END ELSE '' END AS NM_MNGD4 , CASE WHEN AC.ST_MNG5 = 'C' THEN CASE WHEN AC.CD_MNG5 = 'A02' THEN '패션사업본부' ELSE '' END ELSE '' END AS NM_MNGD5 , CASE WHEN AC.ST_MNG6 = 'C' THEN CASE WHEN AC.CD_MNG6 = 'A02' THEN '패션사업본부' ELSE '' END ELSE '' END AS NM_MNGD6 , CASE WHEN AC.ST_MNG7 = 'C' THEN CASE WHEN AC.CD_MNG7 = 'A02' THEN '패션사업본부' ELSE '' END ELSE '' END AS NM_MNGD7 , CASE WHEN AC.ST_MNG8 = 'C' THEN CASE WHEN AC.CD_MNG8 = 'A02' THEN '패션사업본부' ELSE '' END ELSE '' END AS NM_MNGD8 FROM( SELECT ROW_NUMBER() OVER(ORDER BY BizNumber) AS ROW_NO , BizNumber , REPLACE(SettleDate,'-','') AS SettleDate ,[상품매출액] as '51102010' ,[예수부가세] as '21604010' ,[수수료매출] as '51107010' ,[미지급금] as '21102010' -- , CAST(ROUND((OrderSettleAmount) * -1 ,0) AS numeric(19, 4)) AS '51102010'--상품매출액 -- , CAST(ROUND((OrderSettleAmount + CouponPrice - CouponAllotment) * -1 ,0) AS numeric(19, 4)) AS '51102010' -- , CAST(ROUND(MarginSettleAmount -( CouponPrice- CouponAllotment), 0) - ROUND((MarginSettleAmount -( CouponPrice- CouponAllotment)) / 1.1, 0) AS numeric(19,4)) AS '21604010'--예수부가세 -- , CAST(ROUND(MarginSettleAmount - CouponAllotment, 0) - ROUND((MarginSettleAmount - CouponAllotment) / 1.1, 0) AS numeric(19,4)) AS '21604010' -- , CAST(ROUND(((MarginSettleAmount -( CouponPrice- CouponAllotment)) / 1.1), 0) AS numeric(19, 4)) AS '51107010'--수수료매출 -- , CAST(ROUND(((MarginSettleAmount - CouponAllotment) / 1.1), 0) AS numeric(19, 4)) AS '51107010' -- , CAST((Amount) AS numeric(19, 4)) AS '21102010' FROM @SettleList --WHERE OrderSettleAmount > 0 ) AS L UNPIVOT ( AMT FOR CD_ACCT IN ([21604010],[51102010],[51107010],[21102010]) ) AS S CROSS APPLY( SELECT [수수료매출] AS SupAmount --SELECT CAST(ROUND(((MarginSettleAmount -( CouponPrice- CouponAllotment)) / 1.1), 0) AS numeric(19, 4)) AS SupAmount FROM @SettleList T WHERE T.BizNumber = S.BizNumber AND REPLACE(T.SettleDate,'-','') = S.SettleDate ) C INNER JOIN [NEOE].[MA_PARTNER] P ON (S.BizNumber = P.NO_COMPANY AND P.CD_COMPANY = '1000') LEFT OUTER JOIN NEOE.FI_ACCTCODE AC ON (AC.CD_ACCT = S.CD_ACCT AND AC.CD_COMPANY = '1000') WHERE ( @DOCU_Type = 'BOS2' AND s.CD_ACCT IN (21102010, 51102010) ) OR ( @DOCU_Type = 'BOS1' AND s.CD_ACCT IN (21102010, 21604010,51102010,51107010) ) /* 30310:미지급금(거래처) 50100:상품매출 30500:부가세예수금 51100:수수료매출 ★코드변경됨★ 21102010 미지급금(거래처) 21604010 예수부가세 51102010 상품매출액 51107010 수수료매출 상품판매대금정산과 배송비 정산 나오는 항목 수정 2019 05 30 */ IF @@ERROR > 0 BEGIN SET @RESULT = -10004 END ELSE BEGIN SET @RESULT = 10001 END END TRY BEGIN CATCH SET @RESULT = -10004 END CATCH END RETURN